package com.example.easyexcel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.example.easyexcel.handler.CustomCellWriteHandler;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import javax.xml.crypto.Data;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * easyexcel工具类
 *     实现自定义表头并在同一个 sheet中分页
 *
 * @author tu228
 */
public class EasyExcelUtils {

    /**
     * 动态表头与合并单元格测试表格生成
     *
     * @param response 响应对象
     * @param originalFileName 原始文件名称
     * @param sheetName sheet名称
     * @param headDataList 表头数据
     * @param mainDataList 主体表格数据
     */
    public static void dynamicHeadAndMergeExcel(HttpServletResponse response, String originalFileName, String sheetName,
                                                List<CustomCellWriteHandler> customCellWriteHandlerList,
                                                HorizontalCellStyleStrategy headStyleStrategy,
                                                AbstractColumnWidthStyleStrategy columnWidthStyleStrategy,
                                                SimpleRowHeightStyleStrategy rowHeightStyleStrategy,
                                                List<List<String>> headDataList, List<List<Object>> mainDataList) throws Exception {
        // 设置excel响应信息
        setExcelResponse(response, originalFileName);

        try (OutputStream outputStream = response.getOutputStream()) {
            // 创建excel写入构造器
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream);

            // 注册合并策略
            if (CollectionUtils.isNotEmpty(customCellWriteHandlerList))
            for (CustomCellWriteHandler customCellWriteHandler : customCellWriteHandlerList) {
                excelWriterBuilder.registerWriteHandler(customCellWriteHandler);
            }

            // 写入excel
            ExcelWriter excelWriter = excelWriterBuilder.build();
            WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
            WriteTable headTable = headTableCreate(headStyleStrategy, columnWidthStyleStrategy, rowHeightStyleStrategy,
                    headDataList,1);
            excelWriter.write(mainDataList, sheet, headTable);
            // 关闭流，这一步不能放到finally模块中
            excelWriter.finish();
        }
    }

    /**
     * 自定义合并策略列表
     */
    public static List<CustomCellWriteHandler> getCustomCellWriteHandlerList() {
        List<CustomCellWriteHandler> customCellWriteHandlerList = Lists.newArrayList();
        CustomCellWriteHandler customCellWriteHandler01 = new CustomCellWriteHandler(2, 2,
                // 需要合并性别编码、性别描述列
                new int[]{1, 2}, ((cur, pre) -> {
                // 性别编码 && 性别描述相同才需要合并
                String curSexCode = cur.getCell(1).getStringCellValue();
                String preSexCode = pre.getCell(1).getStringCellValue();
                String curSexDesc = cur.getCell(2).getStringCellValue();
                String preSexDesc = pre.getCell(2).getStringCellValue();
                return StringUtils.equals(curSexCode, preSexCode) && StringUtils.equals(curSexDesc, preSexDesc) ? true : false;
            })
        );
        customCellWriteHandlerList.add(customCellWriteHandler01);

        CustomCellWriteHandler customCellWriteHandler02 = new CustomCellWriteHandler(3, 2,
                // 需要合并爱好列
                new int[]{3}, (
                (cur, pre) -> {
                    // 性别编码相同才需要合并
                    String curSexCode = cur.getCell(1).getStringCellValue();
                    String preSexCode = pre.getCell(1).getStringCellValue();
                    return StringUtils.equals(curSexCode, preSexCode) ? true : false;
                })
        );
        customCellWriteHandlerList.add(customCellWriteHandler02);
        return customCellWriteHandlerList;
    }

    /**
     * 设置excel响应信息
     *
     * @param response 响应对象
     * @param originalFileName 原始文件名称
     */
    private static void setExcelResponse(HttpServletResponse response, String originalFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(originalFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

    /**
     * 下载 excel
     *
     * @param response
     * @param list 主体表格数据
     * @throws Exception
     */
    public static void download(HttpServletResponse response, List<?> list) throws Exception {
        // 总共 8 列
        int columnTotal = 8;

        // 首行标题
        List<List<String>> titleData = new ArrayList<>();
        List<String> title = Arrays.asList("这是标题");
        titleData.add(title);

        // 中间抬头部分
        List<List<String>> secondData = new ArrayList<>();
        // 合并前3列, 合并后5列
        List<String> second1 = Arrays.asList("这是抬头1", "", "", "抬头1的内容");
        List<String> second2 = Arrays.asList("这是抬头2", "", "", "抬头2的内容");
        secondData.add(second1);
        secondData.add(second2);

        // 末尾合计
        List<List<String>> totalData = new ArrayList<>();
        List<String> total = new ArrayList<>();
        for (int i = 1; i <= columnTotal; i++) {
            if (i == 1) {
                total.add("合计");
                continue;
            }
            if (i == columnTotal) {
                total.add("这是合计总数");
                continue;
            }
            total.add("");
        }
        totalData.add(total);

        excelCreate(response, list, columnTotal, titleData, secondData, totalData);
    }

    /**
     * excel 表格生成
     *
     * @param response
     * @param list 主体表格数据
     * @param columnTotal 总列数
     * @param titleData 表头数据
     * @param secondData 第二部分抬头数据
     * @param totalData 合计数据
     * @throws Exception
     */
    private static void excelCreate(HttpServletResponse response, List<?> list, int columnTotal, List<List<String>> titleData,
                                    List<List<String>> secondData, List<List<String>> totalData) throws Exception {
        // 这两种ContentType都可以
//        response.setContentType("application/octet-stream");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("Excel文件名称" + DateTimeFormatter.BASIC_ISO_DATE.format(LocalDate.now()), "UTF-8")
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
            WriteSheet sheet = EasyExcel.writerSheet("sheetName").build();

            // 按每5行数据进行分页
            int pageNum = (int) Math.ceil(list.size() / 5d);
            if (pageNum == 0) {
                pageNum = 1;
            }

            // 表格样式
            HorizontalCellStyleStrategy headStyleStrategy = EasyExcelUtils.headStyleStrategy();
            // 列宽样式
            AbstractColumnWidthStyleStrategy columnWidthStyleStrategy = EasyExcelUtils.getColumnWidthStyleStrategy();
            // 行高样式
            SimpleRowHeightStyleStrategy rowHeightStyleStrategy = EasyExcelUtils.getRowHeightStyleStrategy();
            List<List<String>> headDataList = getHeadDataList();
            for (int i = 1; i <= pageNum; i++) {
                WriteTable titleTable = titleTableCreate(columnTotal);
                WriteTable secondTable = secondTableCreate(columnTotal);

                WriteTable headTable = headTableCreate(headStyleStrategy, columnWidthStyleStrategy, rowHeightStyleStrategy,
                        headDataList,i + 1);
                WriteTable totalTable = totalTableCreate(pageNum + 2);

                excelWriter.write(titleData, sheet, titleTable);
                excelWriter.write(secondData, sheet, secondTable);

                int startIndex = (i -1) * 5;
                int endIndex = Math.min(startIndex + 5, list.size());
                excelWriter.write(list.subList(startIndex, endIndex), sheet, headTable);
                excelWriter.write(totalData, sheet, totalTable);
            }
        }
    }

    /**
     * 获取表头数据列表
     */
    private static List<List<String>> getHeadDataList() {
        List<List<String>> head = new ArrayList<>();
        List<String> headList = Arrays.asList("姓名", "性别", "年龄", "身份证号", "地址", "工作单位");
        List<String> subList = Arrays.asList("名称", "工号", "地址");
        headList.forEach(title -> {
            if ("工作单位".equals(title)) {
                subList.forEach(sub -> head.add(Arrays.asList(title, sub)));
                return;
            }
            head.add(Arrays.asList(title, title));
        });

        return head;
    }

    /**
     * 生成标题表格
     *
     * @param columnTotal
     * @return
     */
    private static WriteTable titleTableCreate(int columnTotal) {

        // 合并单元格
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(1, columnTotal, 0);

        // 样式
        WriteCellStyle titleStyle = new WriteCellStyle();
        // 水平居中
        titleStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置白色背景
        titleStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.TRUE);
        font.setFontHeightInPoints((short)14);
        font.setFontName("宋体");
        titleStyle.setWriteFont(font);

        HorizontalCellStyleStrategy titleStyleStrategy = new HorizontalCellStyleStrategy(titleStyle, titleStyle);

        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(0)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(loopMergeStrategy)
                .registerWriteHandler(titleStyleStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return titleTable;
    }

    /**
     * 生成抬头表格
     *
     * @param columnTotal
     * @return
     */
    private static WriteTable secondTableCreate(int columnTotal) {

        // 合并单元格
        LoopMergeStrategy loopMergeStrategy1 = new LoopMergeStrategy(1, 3, 0);
        LoopMergeStrategy loopMergeStrategy2 = new LoopMergeStrategy(1, columnTotal - 3, 3);

        // 样式
        WriteCellStyle secondStyle = new WriteCellStyle();
        // 水平居中
        secondStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        secondStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置白色背景
        secondStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)12);
        font.setFontName("宋体");
        secondStyle.setWriteFont(font);

        HorizontalCellStyleStrategy secondStyleStrategy = new HorizontalCellStyleStrategy(secondStyle, secondStyle);

        // 生成表格
        WriteTable secondTable = EasyExcel.writerTable(1)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(loopMergeStrategy1)
                .registerWriteHandler(loopMergeStrategy2)
                .registerWriteHandler(secondStyleStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return secondTable;
    }

    /**
     * 生成主体数据表格
     *
     * @headStyleStrategy 表格样式
     * @param columnWidthStyleStrategy 列宽样式
     * @param rowHeightStyleStrategy 行高样式
     * @param head 标头数据列表
     * @param tableNum 表格号
     * @return
     */
    private static WriteTable headTableCreate(HorizontalCellStyleStrategy headStyleStrategy,
                                              AbstractColumnWidthStyleStrategy columnWidthStyleStrategy,
                                              SimpleRowHeightStyleStrategy rowHeightStyleStrategy,
                                              List<List<String>> head, int tableNum) {
        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(tableNum)
                .registerWriteHandler(columnWidthStyleStrategy)
                .registerWriteHandler(rowHeightStyleStrategy)
                .registerWriteHandler(headStyleStrategy)
                .head(head)
                .needHead(Boolean.TRUE)
                .build();

        return titleTable;
    }

    /**
     * 获取行高样式
     */
    public static SimpleRowHeightStyleStrategy getRowHeightStyleStrategy() {
        // 行高样式
        SimpleRowHeightStyleStrategy rowHeightStyleStrategy = new SimpleRowHeightStyleStrategy((short) 25, (short) 25);
        return rowHeightStyleStrategy;
    }

    /**
     * 获取列宽样式
     */
    public static AbstractColumnWidthStyleStrategy getColumnWidthStyleStrategy() {
        // 列宽样式
        AbstractColumnWidthStyleStrategy columnWidthStyleStrategy = new AbstractColumnWidthStyleStrategy() {

            // 设置指定列的宽度
            @Override
            protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                Sheet sheet = writeSheetHolder.getSheet();
                int columnIndex = cell.getColumnIndex();
                if (columnIndex == 3 || columnIndex == 4 || columnIndex == 7) {
                    sheet.setColumnWidth(columnIndex, 7000);
                } else {
                    sheet.setColumnWidth(columnIndex, 3600);
                }
            }

        };
        return columnWidthStyleStrategy;
    }

    /**
     * 主体表格样式
     *
     * @return
     */
    public static HorizontalCellStyleStrategy headStyleStrategy() {

        // 主体表头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        // 水平居中
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置灰色背景
        headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.TRUE);
        font.setFontHeightInPoints((short)11);
        font.setFontName("宋体");
        headStyle.setWriteFont(font);

        // 设置边框
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setBorderBottom(BorderStyle.THIN);

        // 主题内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置金色背景
        contentStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        // 自动换行
        contentStyle.setWrapped(true);

        // 设置字体格式
        font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)10);
        font.setFontName("宋体");
        contentStyle.setWriteFont(font);

        // 设置边框
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);
        contentStyle.setBorderBottom(BorderStyle.THIN);


        HorizontalCellStyleStrategy headStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
        return headStyleStrategy;
    }

    /**
     * 生成合计表格
     *
     * @param tableNum
     * @return
     */
    private static WriteTable totalTableCreate(int tableNum) {
        // 样式
        WriteCellStyle totalStyle = new WriteCellStyle();
        // 水平靠左
        totalStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 垂直居中
        totalStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置灰色背景
        totalStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        totalStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 自动换行
        totalStyle.setWrapped(true);

        // 设置字体格式
        WriteFont font = new WriteFont();
        font.setBold(Boolean.FALSE);
        font.setFontHeightInPoints((short)10);
        font.setFontName("宋体");
        totalStyle.setWriteFont(font);

        HorizontalCellStyleStrategy totalStrategy = new HorizontalCellStyleStrategy(null, totalStyle);

        // 生成表格
        WriteTable titleTable = EasyExcel.writerTable(tableNum)
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25, (short)25))
                .registerWriteHandler(totalStrategy)
                .needHead(Boolean.FALSE)
                .build();
        return titleTable;
    }
}
